﻿using System.Data;
using System.Data.SqlClient;
using Blog.Common.Helpers;
using Common.Constant;
using Entity;
using log4net;

namespace DataAccess
{
    public class UserDao
    {
        private static readonly ILog Logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private SqlConnection _sqlCon;
        private DataSet _dataSet;

        public DataSet SelectUsersByUserName(string userName)
        {
            try
            {
                var sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = userName };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetUserByUserName, sqlParameters);
                return _dataSet;
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet SelectAllUser()
        {
            try
            {
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetAllUsers);
                return _dataSet;
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public int CheckUserLogOn(string userName,string password)
        {
            try
            {
                var sqlParametters = new SqlParameter[2];
                sqlParametters[0] = new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = userName };
                sqlParametters[1] = new SqlParameter("@Password", SqlDbType.NVarChar) { Value = password };
                _sqlCon = DBConnection.SqlGetConnection();
                return (int)SqlHelper.ExecuteScalar(_sqlCon, CommandType.StoredProcedure, DBSchemas.CheckUser, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int CheckUserIsExists(string userName)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = userName };
                _sqlCon = DBConnection.SqlGetConnection();
                return (int)SqlHelper.ExecuteScalar(_sqlCon, CommandType.StoredProcedure, DBSchemas.CheckUserIsExists, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public int DeleteUser(string userName)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = userName };
                _sqlCon = DBConnection.SqlGetConnection();
                return (int)SqlHelper.ExecuteScalar(_sqlCon, CommandType.StoredProcedure, DBSchemas.DeleteUser, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int UpdateUser(User user)
        {
            try
            {
                var sqlParametters = new SqlParameter[8];
                sqlParametters[0] = new SqlParameter("@Password", DbType.String) { Value = user.Password };
                sqlParametters[1] = new SqlParameter("@IsAdmin", DbType.Boolean) { Value = user.IsAdmin };
                sqlParametters[2] = new SqlParameter("@Email", DbType.String) { Value = user.Email };
                sqlParametters[3] = new SqlParameter("@FullName", DbType.String) { Value = user.FullName };
                sqlParametters[4] = new SqlParameter("@DateOfBirth", DbType.DateTime) { Value = user.DateOfBirth };
                sqlParametters[5] = new SqlParameter("@AvatarPath", DbType.String) { Value = user.AvatarPath };
                sqlParametters[6] = new SqlParameter("@MoreInfo", DbType.String) { Value = user.MoreInfo };
                sqlParametters[7] = new SqlParameter("@UserName", DbType.String) { Value = user.UserName };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.UpdateUser, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int AddUser(User user)
        {
            try
            {
                var sqlParametters = new SqlParameter[10];
                sqlParametters[0] = new SqlParameter("@UserName", DbType.String) { Value = user.UserName };
                sqlParametters[1] = new SqlParameter("@Password", DbType.String) { Value = user.Password };
                sqlParametters[2] = new SqlParameter("@IsAdmin", DbType.Boolean) { Value = user.IsAdmin };
                sqlParametters[3] = new SqlParameter("@Email", DbType.String) { Value = user.Email };
                sqlParametters[4] = new SqlParameter("@FullName", DbType.String) { Value = user.FullName };
                sqlParametters[5] = new SqlParameter("@DateOfBirth", DbType.DateTime) { Value = user.DateOfBirth };
                sqlParametters[6] = new SqlParameter("@AvatarPath", DbType.String) { Value = user.AvatarPath };
                sqlParametters[7] = new SqlParameter("@JoinDate", DbType.DateTime) { Value = user.JoinDate };
                sqlParametters[8] = new SqlParameter("@MoreInfo", DbType.String) { Value = user.MoreInfo };
                sqlParametters[9] = new SqlParameter("@IsLock", DbType.Boolean) { Value = user.IsLock };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.AddUser, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
    }
}
